package com.geek.jdbc.trans;

/**
 * @author zhoujunjie
 * @desc TODO
 * @date
 */
import com.geek.jdbc.entity.Student;
import com.geek.jdbc.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

public class StudentManager {
    private static final Scanner scanner = new Scanner(System.in);
    private static final String LOGIN_USERNAME = "张无忌";
    private static final String LOGIN_PASSWORD = "8888";

    // 登录方法
    public static boolean login() {
        System.out.println("请输入用户名：");
        String username = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.nextLine();
        return LOGIN_USERNAME.equals(username) && LOGIN_PASSWORD.equals(password);
    }

    // 统计学生人数
    public static int countStudents() {
        String sql = "SELECT COUNT(*) FROM stus";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 查看学生名单（获取所有学生基本信息）
    public static List<Student> listStudents() {
        List<Student> studentList = new ArrayList<>();
        String sql = "SELECT id, name, gender, grade, phone, email, birthday FROM stus";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                Student student = new Student(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("grade"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getDate("birthday")
                );
                studentList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return studentList;
    }

    // 按学号查询学生姓名
    public static String queryNameByStuId(String stuId) {
        String sql = "SELECT name FROM stus WHERE id = ?";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, stuId);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString("name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 按姓名查询学生信息
    public static List<Student> queryStudentsByName(String name) {
        List<Student> studentList = new ArrayList<>();
        String sql = "SELECT id, name, gender, grade, phone, email, birthday FROM stus WHERE name LIKE ?";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "%" + name + "%");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Student student = new Student(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("grade"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getDate("birthday")
                );
                studentList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return studentList;
    }

    // 修改学生出生日期
    public static boolean updateBirthday(String stuId, java.sql.Date newBirthday) {
        String sql = "UPDATE stus SET birthday = ? WHERE id = ?";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDate(1, newBirthday);
            pstmt.setString(2, stuId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 删除学生记录
    public static boolean deleteStudent(String stuId) {
        String sql = "DELETE FROM stus WHERE id = ?";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, stuId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 主菜单及流程控制
    public static void main(String[] args) {
        if (!login()) {
            System.out.println("登录失败！");
            return;
        }
        System.out.println("登录成功！");

        while (true) {
            System.out.println("----------------请选择操作键----------------");
            System.out.println("1. 统计学生人数");
            System.out.println("2. 查看学生名单");
            System.out.println("3. 按学号查询学生姓名");
            System.out.println("4. 按姓名查询学生信息");
            System.out.println("5. 修改学生出生日期");
            System.out.println("6. 删除学生记录");
            System.out.println("0. 退出");
            System.out.print("操作键：");
            String operation = scanner.nextLine();

            switch (operation) {
                case "1":
                    System.out.println("学生总人数：" + countStudents());
                    break;
                case "2":
                    List<Student> students = listStudents();
                    System.out.println("-------------学生列表-------------");
                    System.out.println("学号\t姓名\t性别\t年级\t电话\t\t邮箱\t\t出生日期");
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    for (Student stu : students) {
                        System.out.printf("%s\t%s\t%s\t%s\t%s\t%s\t%s\n",
                                stu.getId(), stu.getName(), stu.getGender(), stu.getGrade(),
                                stu.getPhone(), stu.getEmail(), sdf.format(stu.getBirthday()));
                    }
                    break;
                case "3":
                    System.out.print("请输入学生学号：");
                    String stuIdQueryName = scanner.nextLine();
                    String name = queryNameByStuId(stuIdQueryName);
                    if (name != null) {
                        System.out.println("查询成功！");
                        System.out.println(stuIdQueryName + "号的学生姓名为：" + name);
                    } else {
                        System.out.println("未找到该学号对应的学生！");
                    }
                    break;
                case "4":
                    System.out.print("请输入学生姓名：");
                    String nameQuery = scanner.nextLine();
                    List<Student> studentsByName = queryStudentsByName(nameQuery);
                    if (!studentsByName.isEmpty()) {
                        System.out.println("-------------学生列表-------------");
                        System.out.println("学号\t姓名\t性别\t年级\t电话\t\t邮箱\t\t出生日期");
                        for (Student stu : studentsByName) {
                            System.out.printf("%s\t%s\t%s\t%s\t%s\t%s\t%s\n",
                                    stu.getId(), stu.getName(), stu.getGender(), stu.getGrade(),
                                    stu.getPhone(), stu.getEmail(), formatDate(stu.getBirthday()));
                        }
                    } else {
                        System.out.println("未找到该姓名对应的学生！");
                    }
                    break;
                case "5":
                    System.out.print("请输入学生学号：");
                    String stuIdUpdate = scanner.nextLine();
                    System.out.print("请输入修改后的生日（yyyy-MM-dd）：");
                    String newBirthdayStr = scanner.nextLine();
                    try {
                        java.sql.Date newBirthday = java.sql.Date.valueOf(newBirthdayStr);
                        if (updateBirthday(stuIdUpdate, newBirthday)) {
                            System.out.println("修改成功！");
                        } else {
                            System.out.println("修改失败，可能未找到对应学生！");
                        }
                    } catch (IllegalArgumentException e) {
                        System.out.println("日期格式错误，请按 yyyy-MM-dd 格式输入！");
                    }
                    break;
                case "6":
                    System.out.print("请输入学生学号：");
                    String stuIdDelete = scanner.nextLine();
                    String stuNameDelete = queryNameByStuId(stuIdDelete);
                    if (stuNameDelete != null) {
                        System.out.print("确认要删除学号为" + stuIdDelete + "，姓名为" + stuNameDelete + "的学生信息吗？(Y/N)");
                        String confirm = scanner.nextLine();
                        if ("Y".equalsIgnoreCase(confirm)) {
                            if (deleteStudent(stuIdDelete)) {
                                System.out.println("删除成功！");
                            } else {
                                System.out.println("删除失败！");
                            }
                        } else {
                            System.out.println("取消删除操作！");
                        }
                    } else {
                        System.out.println("未找到该学号对应的学生，无法删除！");
                    }
                    break;
                case "0":
                    System.out.println("退出系统！");
                    return;
                default:
                    System.out.println("无效操作，请重新选择！");
            }
        }
    }
    private static String formatDate(Date date) {
        if (date == null) {
            return "";
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        return sdf.format(date);
    }
}